package com.compty.paint.test.bean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.compty.paint.domain.CityInfo;
import com.compty.paint.domain.CountryInfo;
import com.compty.paint.domain.ProvinceInfo;

public class DBInit {
	
	public static void main(String[] args) {
		
		 List<Country> countryList = getCountry();
		 List<CountryInfo>  CountryInfos = convertCountry(countryList);
		 System.out.println(CountryInfos.size());
		 
		 List<Province> provinceList = getProvince();
		 List<ProvinceInfo>  provinces = convertProvince(provinceList);
		 System.out.println(provinces.size());
		 
		 List<City> cityList = getCity();
		 List<CityInfo>  citys = convertCity(cityList);
		 System.out.println(citys.size());
		 
	}
	
	public static List<CountryInfo> convertCountry(List<Country> countryList){
		
		List<CountryInfo> countrys = new ArrayList<CountryInfo>();
		
		for (Country country : countryList) {
			CountryInfo c = new CountryInfo();
			c.setId(Long.valueOf(country.getId()));
			c.setName(country.getCountryname());
			countrys.add(c);
		}
		
		return countrys;
	}
	
	public static List<ProvinceInfo> convertProvince(List<Province> provinceList){
		
		List<ProvinceInfo> provinces = new ArrayList<ProvinceInfo>();
		
		for (Province province : provinceList) {
			
			ProvinceInfo p = new ProvinceInfo();
			p.setId(Long.valueOf(province.getPid()));
			p.setName(province.getPname());
			provinces.add(p);
		}
		
		return provinces;
	}
	
	public static List<CityInfo> convertCity(List<City> cityList){
		
		List<CityInfo> citys = new ArrayList<CityInfo>();
		
		for (City city : cityList) {
			CityInfo c = new CityInfo();
			
			c.setId(Long.valueOf(city.getId()));
			c.setName(city.getCname());
			citys.add(c);
		}
		
		return citys;
	}
	
	
	
	public static List<Country> getCountry(){
		
		List<Country> returnList = new ArrayList<Country>();
		
		Connection conn = getConnection();
		
		String sql = "select * from t_countryinfo ";
		
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.execute();
			ResultSet rs = ps.getResultSet();
			while(rs.next()){
				Country c = new Country();
				
				c.setId(rs.getInt("id"));
				c.setCountrycode(rs.getString("countrycode"));
				c.setCountryname(rs.getString("countryname"));
				c.setPingyin(rs.getString("pingyin"));
				
				returnList.add(c);
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return returnList;
	}
	
	public static List<Province> getProvince(){
		List<Province> returnList = new ArrayList<Province>();
		
		Connection conn = getConnection();
		
		String sql = "select * from t_province ";
		
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.execute();
			ResultSet rs = ps.getResultSet();
			while(rs.next()){
				Province p = new Province();
				
				p.setCountryid(rs.getInt("countryid"));
				p.setPcode(rs.getString("pcode"));
				p.setPid(rs.getInt("pid"));
				p.setPingyin(rs.getString("pingyin"));
				p.setPname(rs.getString("pname"));
				
				returnList.add(p);
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return returnList;
	}
	
	public static List<City> getCity(){
		List<City> returnList = new ArrayList<City>();
		
		Connection conn = getConnection();
		
		String sql = "select * from t_city ";
		
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.execute();
			ResultSet rs = ps.getResultSet();
			while(rs.next()){
				City c = new City();
				
				c.setCitycode(rs.getString("citycode"));
				c.setCname(rs.getString("cname"));
				c.setId(rs.getInt("id"));
				c.setPingyin(rs.getString("pingyin"));
				c.setProvinceid(rs.getInt("provinceid"));
				
				String sql1 = "select * from t_province where pid=?";
				PreparedStatement ps1  = conn.prepareStatement(sql1);
				ps1.setInt(1, c.getProvinceid());
				ResultSet rs1 = ps1.executeQuery();
				while(rs1.next()){
					String pname = rs1.getString("pname");
					c.setProvinceName(pname);
				}
				
				returnList.add(c);
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return returnList;
	}
	
	public static  Connection getConnection(){
		Connection con;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = java.sql.DriverManager
					.getConnection(
							"jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8",
							"root", "123");
			
			return con; 
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return null;
	}
	
	public static  Connection getConnection1(){
		Connection con;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = java.sql.DriverManager
					.getConnection(
							"jdbc:mysql://localhost:3306/paint?useUnicode=true&characterEncoding=utf-8",
							"root", "123");
			
			return con; 
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return null;
	}
	
}
